import psycopg2

import dbconfig
import models


class MyConch:
    def __init__(self):
        self._con = psycopg2.connect(database=dbconfig.db_name, user=dbconfig.db_user, password=dbconfig.db_pwd, host=dbconfig.db_host, port=dbconfig.db_port)

    def add(self, item: models.MyItem):
        cur = self._con.cursor()
        cur.execute("INSERT INTO public.\"MyConch\" (\"Title\",\"Price\",\"Time\", \"category_id\") \
           VALUES (%(title)s, %(price)s, %(time)s, %(category_id)s)", item.__dict__)
        self._con.commit()

        self._con.close()
        return True

    def update(self, item: models.MyItem):
        cur = self._con.cursor()
        cur.execute(""" update public."MyConch" set "Title"=%(title)s, "Price"=%(price)s, "Time"=%(time)s, "category_id"=%(category_id)s where "Id" = %(id)s """, item.__dict__)
        self._con.commit()

        self._con.close()
        return True

    def get_all(self):
        cur = self._con.cursor()
        cur.execute(""" select a1."Id", a1."Title", a1."Price", a1."Time", a1."category_id", a2."Name" from public."MyConch" a1 left join public."category" a2 on a1."category_id" = a2."Id" order by "Time" desc """)
        rs = []
        rows = cur.fetchall()
        for row in rows:
            rs.append({"id": row[0], "title": row[1], "price": row[2], "time": row[3], "category_id": row[4], "category_name": row[5]})
        self._con.close()
        return rs

    def add_category(self, item: models.Category):
        cur = self._con.cursor()
        cur.execute("INSERT INTO public.\"category\" (\"Name\",\"ParentId\") \
                   VALUES (%(name)s, %(parentId)s )", item.__dict__)
        self._con.commit()

        self._con.close()
        return True

    def get_category(self):
        cur = self._con.cursor()
        cur.execute(""" SELECT "Id", "Name", "ParentId" FROM public."category" order by "ParentId" """)
        rs = []
        dic = {}
        rows = cur.fetchall()
        for row in rows:
            did = row[0]
            pid = row[2]
            data = {"id": row[0], "name": row[1], "parentId": pid , "childs":[]}
            dic[str(did)] =  data
            if pid > 0:
                dic[str(pid)]["childs"].append(data)
            else:
                rs.append(data)
        self._con.close()
        return rs
